package com.imis.base.util.table.service.impl;

import com.imis.base.constant.CommonConstant;
import com.imis.base.constant.DataBaseConstant;
import com.imis.base.util.ConvertUtils;
import com.imis.base.util.table.model.ColumnMeta;
import com.imis.base.util.table.service.IDataBaseTableHandleService;
import com.imis.module.online.table.model.po.TableIndex;

/**
 * <p>
 * DataBaseTableSqlServerHandleServiceImpl<br>
 * SqlServer 数据库表处理服务 实现
 * </p>
 *
 * @author XinLau
 * @version 1.0
 * @since 2020年09月28日 17:36
 */
public class DataBaseTableSqlServerHandleServiceImpl implements IDataBaseTableHandleService {

    /**
     * 将get...FieldDesc这些方法中共同的内容抽出来
     *
     * @param formColumnMeta - 前端表单
     * @param dataColumnMeta - 数据库表
     * @return String - SQL语句
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/10 14:17
     */
    private String getFieldDesc(final ColumnMeta formColumnMeta, final ColumnMeta dataColumnMeta) {
        StringBuilder sql = new StringBuilder();
        if (formColumnMeta.getColumnType().equalsIgnoreCase(DataBaseConstant.STRING)) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.NVARCHAR).append(DataBaseConstant.LEFT_BRACKET).append(formColumnMeta.getColumnSize()).append(DataBaseConstant.RIGHT_BRACKET).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        } else if (DataBaseConstant.DATE_.equalsIgnoreCase(formColumnMeta.getColumnType())) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.DATE_TIME_).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        } else if (DataBaseConstant.INT.equalsIgnoreCase(formColumnMeta.getColumnType())) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.INT).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        } else if (formColumnMeta.getColumnType().equalsIgnoreCase(DataBaseConstant.DOUBLE)) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.FLOAT_).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        } else if (DataBaseConstant.BIG_DECIMAL_.equalsIgnoreCase(formColumnMeta.getColumnType())) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.NUMERIC_).append(DataBaseConstant.LEFT_BRACKET).append(formColumnMeta.getColumnSize()).append(DataBaseConstant.COMMA).append(formColumnMeta.getDecimalDigits()).append(DataBaseConstant.RIGHT_BRACKET).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        } else if (DataBaseConstant.TEXT_.equalsIgnoreCase(formColumnMeta.getColumnType())) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.NTEXT).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        } else if (DataBaseConstant.BLOB_.equalsIgnoreCase(formColumnMeta.getColumnType())) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.IMAGE);
        }
        return sql.toString();
    }

    /**
     * 获取添加字段描述
     *
     * @param formColumnMeta - 前端表单
     * @return String - SQL语句
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/10 14:17
     */
    private String getAddFieldDesc(final ColumnMeta formColumnMeta) {
        return getFieldDesc(formColumnMeta, null);
    }

    /**
     * 获取重命名字段描述
     *
     * @param formColumnMeta - 前端表单
     * @return String - SQL语句
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/10 14:17
     */
    private String getRenameFieldDesc(final ColumnMeta formColumnMeta) {
        StringBuilder sql = new StringBuilder();
        if (formColumnMeta.getColumnType().equalsIgnoreCase(DataBaseConstant.STRING)) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.NVARCHAR).append(DataBaseConstant.LEFT_BRACKET).append(formColumnMeta.getColumnSize()).append(DataBaseConstant.RIGHT_BRACKET).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        } else if (DataBaseConstant.DATE_.equalsIgnoreCase(formColumnMeta.getColumnType())) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.DATE_TIME_).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        } else if (DataBaseConstant.INT.equalsIgnoreCase(formColumnMeta.getColumnType())) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.INT).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        } else if (formColumnMeta.getColumnType().equalsIgnoreCase(DataBaseConstant.DOUBLE)) {
            sql.append(formColumnMeta.getColumnName()).append(DataBaseConstant.SPACE).append(DataBaseConstant.FLOAT_).append(DataBaseConstant.SPACE).append(CommonConstant.TO_UPPER_CASE_Y.equals(formColumnMeta.getIsNullable()) ? DataBaseConstant.SQL_NULL : DataBaseConstant.SQL_NOT_NULL);
        }
        return sql.toString();
    }

    /**
     * 获取更新字段描述
     *
     * @param formColumnMeta - 前端表单
     * @return String - SQL语句
     * @author XinLau
     * @creed The only constant is change ! ! !
     * @since 2020/10/10 14:17
     */
    private String getUpdateFieldDesc(final ColumnMeta formColumnMeta, final ColumnMeta dataColumnMeta) {
        return getFieldDesc(formColumnMeta, dataColumnMeta);
    }

    @Override
    public String getAddColumnSql(final ColumnMeta columnMeta) {
        return DataBaseConstant.SQL_ADD + getAddFieldDesc(columnMeta) + DataBaseConstant.SEMICOLON;
    }

    @Override
    public String getReNameFieldName(final ColumnMeta columnMeta) {
        // sp_rename 'TOA_E_ARTICLE.version','processVersion','COLUMN';
        return " sp_rename " + DataBaseConstant.SINGLE_QUOTE + columnMeta.getTableName() + DataBaseConstant.DOT + columnMeta.getOldColumnName() + DataBaseConstant.SINGLE_QUOTE + DataBaseConstant.COMMA + DataBaseConstant.SPACE + DataBaseConstant.SINGLE_QUOTE + columnMeta.getColumnName() + DataBaseConstant.SINGLE_QUOTE + DataBaseConstant.COMMA + DataBaseConstant.SPACE + DataBaseConstant.SINGLE_QUOTE + DataBaseConstant.SQL_COLUMN + DataBaseConstant.SINGLE_QUOTE + DataBaseConstant.SEMICOLON;
    }

    @Override
    public String getUpdateColumnSql(final ColumnMeta formColumnMeta, final ColumnMeta dataColumnMeta) {
        return DataBaseConstant.SQL_ALTER + DataBaseConstant.SQL_COLUMN + getUpdateFieldDesc(formColumnMeta, dataColumnMeta) + DataBaseConstant.SEMICOLON;
    }

    @Override
    public String getMatchClassTypeByDataType(final String dataType, final int digits) {
        String result = "";
        if (DataBaseConstant.VARCHAR.equalsIgnoreCase(dataType) || DataBaseConstant.NVARCHAR.equalsIgnoreCase(dataType)) {
            result = DataBaseConstant.STRING;
        } else if (DataBaseConstant.FLOAT_.equalsIgnoreCase(dataType)) {
            result = DataBaseConstant.DOUBLE;
        } else if (DataBaseConstant.INT.equalsIgnoreCase(dataType)) {
            result = DataBaseConstant.INT;
        } else if (DataBaseConstant.DATE.equalsIgnoreCase(dataType)) {
            result = DataBaseConstant.DATE_;
        } else if (DataBaseConstant.DATE_TIME.equalsIgnoreCase(dataType)) {
            result = DataBaseConstant.DATE_;
        } else if (DataBaseConstant.NUMERIC.equalsIgnoreCase(dataType)) {
            result = DataBaseConstant.BIG_DECIMAL_;
        } else if (DataBaseConstant.VARBINARY.equalsIgnoreCase(dataType) || DataBaseConstant.IMAGE.equalsIgnoreCase(dataType)) {
            result = DataBaseConstant.BLOB_;
        } else if (DataBaseConstant.TEXT_.equalsIgnoreCase(dataType) || DataBaseConstant.NTEXT.equalsIgnoreCase(dataType)) {
            result = DataBaseConstant.TEXT_;
        }
        return result;
    }

    @Override
    public String dropTableSql(final String tableName) {
        return DataBaseConstant.SQL_DROP + DataBaseConstant.SQL_TABLE + tableName + DataBaseConstant.SEMICOLON;
    }

    @Override
    public String getDropColumnSql(final String fieldName) {
        return DataBaseConstant.SQL_DROP + DataBaseConstant.SQL_COLUMN + fieldName + DataBaseConstant.SEMICOLON;
    }

    @Override
    public String getColumnCommentSql(final ColumnMeta columnMeta) {
        StringBuilder commentSql = new StringBuilder("EXECUTE ");
        if (ConvertUtils.isEmpty(columnMeta.getOldColumnName())) {
            commentSql.append("sp_addextendedproperty");
        } else {
            commentSql.append("sp_updateextendedproperty");
        }
        commentSql.append(" N'MS_Description', '");
        commentSql.append(columnMeta.getColumnComment());
        commentSql.append("', N'SCHEMA', N'dbo', N'TABLE', N'");
        commentSql.append(columnMeta.getTableName());
        commentSql.append("', N'COLUMN', N'");
        commentSql.append(columnMeta.getColumnName()).append(DataBaseConstant.SINGLE_QUOTE);
        return commentSql.toString();
    }

    @Override
    public String getSpecialHandle(final ColumnMeta formColumnMeta, final ColumnMeta dataColumnMeta) {
        return null;
    }

    @Override
    public String creatIndexes(final TableIndex tableIndex, final String tableName) {
        StringBuilder sql = new StringBuilder();
        if (!CommonConstant.DEL_FLAG_DELETE.equals(tableIndex.getDelFlag()) && CommonConstant.TO_UPPER_CASE_N.equals(tableIndex.getIsSynchronize())) {
            String indexName = tableIndex.getIndexName();
            String indexField = tableIndex.getIndexField();
            String indexType = DataBaseConstant.NORMAL_SYNCHRONIZATION.equals(tableIndex.getIndexType()) ? DataBaseConstant.SQL_INDEX : tableIndex.getIndexType() + DataBaseConstant.SQL_INDEX;
            sql.append(DataBaseConstant.SQL_CREATE).append(indexType).append(indexName).append(DataBaseConstant.SQL_ON).append(tableName).append(DataBaseConstant.LEFT_BRACKET).append(indexField).append(DataBaseConstant.RIGHT_BRACKET);
        }
        return sql.toString();
    }

    @Override
    public String dropIndexes(final String indexName, final String tableName) {
        return DataBaseConstant.SQL_DROP + DataBaseConstant.SQL_INDEX + indexName + DataBaseConstant.SQL_ON + tableName;
    }

    @Override
    public String countIndex(final String indexName, final String tableName) {
        return DataBaseConstant.SQL_SELECT + DataBaseConstant.SQL_COUNT + DataBaseConstant.SQL_FROM + "sys.indexes" + DataBaseConstant.SQL_WHERE + "object_id" + DataBaseConstant.SPACE + DataBaseConstant.EQUALS + DataBaseConstant.SPACE + "OBJECT_ID" + DataBaseConstant.LEFT_BRACKET + DataBaseConstant.SINGLE_QUOTE + tableName + DataBaseConstant.SINGLE_QUOTE + DataBaseConstant.RIGHT_BRACKET + DataBaseConstant.SQL_AND + "NAME" + DataBaseConstant.SPACE + DataBaseConstant.EQUALS + DataBaseConstant.SPACE + DataBaseConstant.SINGLE_QUOTE + indexName + DataBaseConstant.SINGLE_QUOTE;
    }

}
